package com.xms.datacore;

import com.xms.core.exception.XmsException;
import com.xms.datacore.provider.IDataBase;
import com.xms.schema.core.AttributeTypeIds;
import com.xms.schema.core.EntityMask;
import com.xms.schema.entity.Attribute;
import com.xms.schema.entity.Entity;
import com.xms.schema.entity.RelationShip;
import org.apache.ibatis.session.SqlSession;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;

/**
 * <p>
 * MsSqlMetadataProvider
 * </p>
 *
 * @author migo
 * @since 2021/12/7
 */
@Service
public class MsSqlMetadataProvider implements IMetadataProvider {
    private Logger logger = LoggerFactory.getLogger(MsSqlMetadataProvider.class);
    private IDataBase _database;

    @Autowired
    public MsSqlMetadataProvider(IDataBase myDataBase) {
        _database = myDataBase;
    }

    @Override
    public void addColumn(Attribute... attributes) throws XmsException, SQLException {
        Assert.notEmpty(Arrays.asList(attributes), "attributes must not be empty");
        if (!tableExists(attributes[0].getEntityname())) {
            return;
        }
        StringBuilder sql = new StringBuilder();
        for (Attribute attr : attributes) {
            if (!this.columnExists(attr.getEntityname(), attr.getName())) {
                sql.append(String.format("ALTER TABLE %s ADD %s %s"
                        , getSchemaTableName(attr.getEntityname()), attr.getName(), getDbType(attr)));
            }
        }
        if (sql.length() > 0) {
            this.execute(sql.toString());
        }
    }

    @Override
    public void alterColumn(Attribute... attributes) throws SQLException {
        if (!tableExists(attributes[0].getEntityname())) {
            return;
        }
        for (Attribute attr : attributes) {
            if (this.columnExists(attr.getEntityname(), attr.getName())) {
                this.execute(String.format("ALTER TABLE %s ALTER COLUMN %s %s"
                        , getSchemaTableName(attr.getEntityname()), attr.getName(), getDbType(attr)));
            }
        }
    }

    @Override
    public boolean columnExists(String table, String column) throws SQLException {
        ResultSet resultSet = this.executeQuery(String.format("select count(1) from syscolumns where id=Object_Id('%s') and name='%s'", table, column));
        if (resultSet.next()) {
            return resultSet.getInt(1) > 0;
        }
        return false;
    }

    @Override
    public void dropColumn(Attribute... attributes) throws SQLException {
        if (!tableExists(attributes[0].getEntityname())) {
            return;
        }
        StringBuilder dropSql = new StringBuilder();
        for (Attribute attr : attributes) {
            if (this.columnExists(attr.getEntityname(), attr.getName())) {
                dropSql.append(String.format("ALTER TABLE %s DROP COLUMN %s",
                        getSchemaTableName(attr.getEntityname()), attr.getName()));
            }
        }
        if (dropSql.length() > 0) {
            this.execute(dropSql.toString());
        }
    }

    @Override
    public void alterView(Entity entity, List<Attribute> attributes, List<RelationShip> relationShips) throws SQLException {
        if (!tableExists(entity.getName())) {
            return;
        }
        dropView(entity);
        this.createView(entity, attributes, relationShips);
    }

    @Override
    public void createView(Entity entity, List<Attribute> attributes, List<RelationShip> relationShips) throws SQLException {
        if (!tableExists(entity.getName())) {
            return;
        }
        StringBuilder createSql = new StringBuilder();
        createSql.append(String.format("CREATE VIEW %sview AS ", getSchemaTableName(entity.getName())));
        createSql.append("\nSELECT ");

        List<String> attrNames = new ArrayList<>();
        for (Attribute item : attributes) {
            if (this.columnExists(entity.getName(), item.getName())) {
                attrNames.add(String.format("%s.%s", entity.getName(), item.getName()));
            }
        }

        for (RelationShip item : relationShips) {
            if (attributes.stream().anyMatch(n -> n.getAttributeid().equals(item.getReferencingattributeid()))) {
                attrNames.add(String.format("%s.name AS %sname", item.getName(), item.getReferencingattributename()));
            }
        }

        createSql.append(String.join(",\n", attrNames));
        //tables
        createSql.append(String.format("\nFROM %s", getSchemaTableName(entity.getName())));
        for (RelationShip item : relationShips) {
            createSql.append(String.format("\nLEFT JOIN %s AS %s ON %s.%s=%s.%s",
                    item.getReferencedentityname(), item.getName()
                    , entity.getName(), item.getReferencingattributename()
                    , item.getName(), item.getReferencedattributename()));
        }
        this.execute(createSql.toString());
    }

    @Override
    public void createTable(Entity entity, List<Attribute> defaultAttributes) throws SQLException {
        if (this.tableExists(entity.getName())) {
            return;
        }
        StringBuilder s = new StringBuilder();
        s.append(String.format("CREATE TABLE %s(", getSchemaTableName(entity.getName())));
        for (Attribute attribute : defaultAttributes) {
            s.append(String.format("%s %s %s NULL,",
                    attribute.getName(), getDbType(attribute), attribute.getIsnullable() ? "" : "NOT"));
        }
        List<String> idxColumns = new LinkedList<>();//需要创建索引的字段
        if (defaultAttributes.stream().anyMatch(n -> n.getName().equals("statecode"))) {
            idxColumns.add("statecode");
        }
        if (defaultAttributes.stream().anyMatch(n -> n.getName().equals("createdon"))) {
            idxColumns.add("createdon");
        }
        if (entity.getEntitymask() == EntityMask.User.value()) {
            if (defaultAttributes.stream().anyMatch(n -> n.getName().equals("ownerid"))) {
                if (!defaultAttributes.stream().anyMatch(n -> n.getName().equals("owneridtype"))) {
                    s.append("owneridtype int NOT NULL,");
                }
                idxColumns.add("ownerid");
            }
        }
        if (defaultAttributes.stream().anyMatch(n -> n.getName().equals("organizationid"))) {
            idxColumns.add("organizationid");
        }
        //主键
        s.append(String.format("CONSTRAINT pk_%s PRIMARY KEY CLUSTERED", entity.getName()));
        s.append("(")
                .append(String.format("[%sid] ASC", entity.getName()))
                .append(")WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]");
        s.append(");");
        //创建外键约束
        if (defaultAttributes.stream().anyMatch(n -> n.getName().equals("createdby"))) {
            s.append(String.format("ALTER TABLE %s WITH CHECK ADD  CONSTRAINT [fk_%s_systemUser_createdby] FOREIGN KEY([createdby])", getSchemaTableName(entity.getName()), entity.getName()));
            s.append("REFERENCES[dbo].[systemuser]([systemuserid]);");
            s.append(String.format("ALTER TABLE %s CHECK CONSTRAINT [fk_%s_systemUser_createdby];", getSchemaTableName(entity.getName()), entity.getName()));
        }
        if (entity.getEntitymask() == EntityMask.User.value()) {
            if (defaultAttributes.stream().anyMatch(n -> n.getName().equals("owningbusinessunit"))) {
                s.append(String.format("ALTER TABLE %s WITH NOCHECK ADD CONSTRAINT [fk_%s_businessunit_owningbusinessunit] FOREIGN KEY([owningbusinessunit])", getSchemaTableName(entity.getName()), entity.getName()));
                s.append("REFERENCES[dbo].[BusinessUnit]([BusinessUnitId]);");
                s.append(String.format("ALTER TABLE %s CHECK CONSTRAINT [fk_%s_businessunit_owningbusinessunit];", getSchemaTableName(entity.getName()), entity.getName()));
            }
        }
        if (defaultAttributes.stream().anyMatch(n -> n.getName().equals("organizationid"))) {
            s.append(String.format("ALTER TABLE %s WITH NOCHECK ADD CONSTRAINT [fk_%s_organization_organizationid] FOREIGN KEY([organizationid])", getSchemaTableName(entity.getName()), entity.getName()));
            s.append("REFERENCES[dbo].[organization]([organizationid]);");
            s.append(String.format("ALTER TABLE %s CHECK CONSTRAINT [fk_%s_organization_organizationid];", getSchemaTableName(entity.getName()), entity.getName()));
        }

        //启动流程时建立约束
        if (entity.getWorkflowenabled()) {
            if (defaultAttributes.stream().anyMatch(n -> n.getName().equals("workflowid"))) {
                s.append(String.format("ALTER TABLE %s WITH NOCHECK ADD CONSTRAINT [fk_%s_workflow_workflowid] FOREIGN KEY([workflowid])", getSchemaTableName(entity.getName()), entity.getName()));
                s.append("REFERENCES[dbo].[WorkFlow]([WorkFlowId]);");
                s.append(String.format("ALTER TABLE %s CHECK CONSTRAINT [fk_%s_workflow_workflowid];", getSchemaTableName(entity.getName()), entity.getName()));
            }
        }
        this.execute(s.toString());
    }

    @Override
    public boolean tableExists(String table) throws SQLException {
        ResultSet resultSet = this.executeQuery(String.format("SELECT count(1) FROM sysobjects where xtype='U' and name ='%s'", table));
        if (resultSet.next()) {
            return resultSet.getInt(1) > 0;
        }
        return false;
    }

    @Override
    public boolean viewExists(String name) throws SQLException {
        ResultSet resultSet = this.executeQuery(String.format("SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[%s]') AND OBJECTPROPERTY(id, N'IsView') = 1", name));
        if (resultSet.next()) {
            return resultSet.getInt(1) > 0;
        }
        return false;
    }

    @Override
    public void dropTable(Entity entity) throws SQLException {
        if (!this.tableExists(entity.getName())) {
            return;
        }
        StringBuilder dropSql = new StringBuilder();
        dropSql.append(String.format("DROP TABLE %s", getSchemaTableName(entity.getName())));
        this.execute(dropSql.toString());
    }

    @Override
    public void dropView(Entity entity) throws SQLException {
        this.dropView(entity.getName() + "view");
    }

    @Override
    public void dropView(String name) throws SQLException {
        if (!this.viewExists(name)) {
            return;
        }
        StringBuilder dropSql = new StringBuilder();
        dropSql.append(String.format("DROP VIEW %s", getSchemaTableName(name)));
        this.execute(dropSql.toString());
    }

    private void execute(String s) throws SQLException {
        SqlSession session = _database.getSqlSession();
        try {
            logger.info(s);
            _database.execute(session, s);
        } catch (Exception e) {
            throw e;
        } finally {
            _database.closeSqlSession(session);
        }
    }

    private ResultSet executeQuery(String s) throws SQLException {
        SqlSession session = _database.getSqlSession();
        try {
            logger.info(s);
            return _database.executeQuery(session, s);
        } catch (Exception e) {
            throw e;
        } finally {
            _database.closeSqlSession(session);
        }
    }

    private String getSchemaTableName(String name) {
        return "dbo." + name;
    }

    private String getDbType(Attribute attr) {
        String result = "";
        switch (attr.getAttributetypename()) {
            case AttributeTypeIds.FLOAT:
            case AttributeTypeIds.MONEY:
            case AttributeTypeIds.SMALLMONEY:
                result = String.format("numeric(23,%d)", attr.getPrecision());
                break;

            case AttributeTypeIds.NVARCHAR:
                result = String.format("nvarchar(%d)", attr.getMaxlength());
                break;

            case AttributeTypeIds.TEXT:
            case AttributeTypeIds.NTEXT:
                result = "text";
                break;

            case AttributeTypeIds.VARCHAR:
                result = String.format("varchar(%d)", attr.getMaxlength());
                break;

            case AttributeTypeIds.CHAR:
                result = String.format("char(%d)", attr.getMaxlength());
                break;

            case AttributeTypeIds.NCHAR:
                result = String.format("nchar(%d)", attr.getMaxlength());
                break;

            case AttributeTypeIds.BIT:
            case AttributeTypeIds.STATE:
                result = "bit";
                break;

            case AttributeTypeIds.INT:
            case AttributeTypeIds.SMALLINT:
            case AttributeTypeIds.PICKLIST:
            case AttributeTypeIds.STATUS:
                result = "int";
                break;

            case AttributeTypeIds.CUSTOMER:
            case AttributeTypeIds.OWNER:
            case AttributeTypeIds.PRIMARYKEY:
            case AttributeTypeIds.LOOKUP:
            case AttributeTypeIds.UNIQUEIDENTIFIER:
                result = "uniqueidentifier";
                break;

            case AttributeTypeIds.TIMESTAMP:
            case AttributeTypeIds.SMALLDATETIME:
            case AttributeTypeIds.DATETIME:
                result = "datetime";
                break;

            default:
                result = attr.getAttributetypename();
                break;
        }
        return result;
    }
}
